import ctypes
import os
import re
import shutil
import time
import win32gui

import pyautogui
import win32con
import win32com.client
from PyPDF2 import PdfFileReader, PdfFileWriter
import pdfplumber
import pandas as pd
from pandas import ExcelWriter
import xlwings as xw
import arrow
from openpyxl.utils import get_column_letter, column_index_from_string
import tkinter as tk
from robot import Robot
import warnings


warnings.filterwarnings("ignore")
Robot_d = Robot()


def login_erp():
    try:
        Robot_d.ie_ie('http://erp.chinapost.com.cn:8000/')
        time.sleep(5)
        Robot_d.input_name('usernameField', username)
        Robot_d.input_name('passwordField', psw)
        time.sleep(1)
        Robot_d.click_id('btn_submit')  # 确定
        time.sleep(10)
        Robot_d.img_click('E:/Sorter/image2/总账会计.png')
        time.sleep(3)
        Robot_d.img_click('E:/Sorter/image2/输入日记账.png')
        time.sleep(10)
        pyautogui.hotkey('Alt', 'V')
        pyautogui.hotkey('Shift', 'R')
        pyautogui.hotkey('Shift', 'N')
        pyautogui.hotkey('Shift', 'O')
        Robot.key_txt('CUX:凭证打印（按来源）')
        pyautogui.press('Tab')
        pyautogui.press('Tab')
        Robot.key_txt(nowdate)
        pyautogui.press('Tab', 6, 1)
        Robot.key_txt(dept)
        pyautogui.press('Enter')
        pyautogui.hotkey('Shift', 'M')
        pyautogui.press('Tab')
        pyautogui.press('Enter')
        pyautogui.hotkey('Shift', 'I')
        pyautogui.hotkey('Shift', 'R')

        asd = 0
        while True:
            # 截图是否更新完，更新完后下载
            time.sleep(5)
            Robot_d.move(100, 130, 1)
            try:
                m, n, o, p = pyautogui.locateOnScreen('E:/Sorter/image2/凭证索引完成.png', confidence=0.8)
                # print(x, y, x + a, y + b)
                im = pyautogui.screenshot(region=(m, n, o, p))
                im.save('E:/Sorter/image3/凭证索引完成.png')
                c = Robot_d.img_Identify('E:/Sorter/image2/凭证索引完成.png', 'E:/Sorter/image3/凭证索引完成.png')
                print(c)
                if c is True:
                    break
                else:
                    pass
            except Exception as e:
                print(e)
                asd += 1
                if asd == 20:
                    raise Exception('again')
                else:
                    pass
            # 加结束条件
        pyautogui.hotkey('Shift', 'P')
        Robot_d.img_click('E:/Sorter/image2/保存.png')
        path_file = 'C:/Users/admin/Downloads/'
        lists = os.listdir(path_file)
        lists.sort(key=lambda fn: os.path.getmtime(path_file + fn))
        file_newest = path_file + lists[-1]
        print(file_newest)
        shutil.move(file_newest, path + '/CUX_报销报账凭证索引.xls')
    except:
        os.system(r"taskkill /F /IM iexplore.exe")
        login_erp()


def pdf_index():
    win_on()


def erp_all():

    win_on()


def erp_origin():
    origins = pd.read_excel('D:/PZSets/user.xlsx', sheet_name='Sheet2')
    origin = origins['来源'].values
    for o in origin():
        win_on()
        pyautogui.hotkey('Shift', 'M')
        pyautogui.press('Enter')
        Robot.key_txt('CUX:邮速整合总账凭证编号索引表')
        pyautogui.press('Tab')
        Robot.key_txt('YW_LEDGER')
        pyautogui.press('Tab')
        Robot.key_txt(dept)
        pyautogui.press('Tab')
        Robot.key_txt(nowdate)
        pyautogui.press('Tab', 3, 1)
        Robot.key_txt(o)
        pyautogui.press('Tab')
        pyautogui.press('Enter')
        pyautogui.hotkey('Shift', 'M')
        pyautogui.press('Tab')
        pyautogui.press('Enter')
        pyautogui.hotkey('Shift', 'I')
        pyautogui.hotkey('Shift', 'R')
        asd = 0
        while True:
            # 截图是否更新完，更新完后下载
            time.sleep(5)
            Robot_d.move(100, 130, 1)
            try:
                m, n, o, p = pyautogui.locateOnScreen('E:/Sorter/image2/邮速整合完成.png', confidence=0.8)
                # print(x, y, x + a, y + b)
                im = pyautogui.screenshot(region=(m, n, o, p))
                im.save('E:/Sorter/image3/邮速整合完成.png')
                c = Robot_d.img_Identify('E:/Sorter/image2/邮速整合完成.png', 'E:/Sorter/image3/邮速整合完成.png')
                print(c)
                if c is True:
                    break
                else:
                    pass
            except Exception as e:
                print(e)
                asd += 1
                if asd == 20:
                    raise Exception('again')
                else:
                    pass
            # 加结束条件
        pyautogui.press('Down')
        pyautogui.hotkey('Shift', 'P')
        pyautogui.press('Enter')
        pyautogui.hotkey('Alt', 'S')

        path_file = 'C:/Users/admin/Downloads/'
        lists = os.listdir(path_file)
        lists.sort(key=lambda fn: os.path.getmtime(path_file + fn))
        file_newest = path_file + lists[-1]
        print(file_newest)
        shutil.move(file_newest, path + '/CUX_邮速整合总账凭证_' + o + '.xls')


def erp_index():

    # 读取报销报账
    bxbzpath = path + '/CUX_报销报账凭证索引.xls'
    app = xw.App(visible=False, add_book=False)
    target1 = app.books.open(bxbzpath)
    sht1 = target1.sheets[0]
    rows1 = sht1.used_range.last_cell.row
    cols1 = sht1.used_range.last_cell.column
    col_name1 = get_column_letter(cols1)
    fanwei1 = 'A4:' + str(col_name1) + str(rows1)
    gv_excel1 = target1.sheets[0].range(fanwei1).value
    target1.close()
    df1 = pd.DataFrame(gv_excel1, columns=gv_excel1[0])
    df1 = df1.drop(index=[0])
    print(df1)

    df2 = pd.DataFrame()
    df2['公司名称'] = dept
    df2['凭证张数'] = ''
    df2['凭证编号'] = df1['凭证号']
    df2['附件个数'] = ''
    df2['附件编号'] = df1['附件编号']

    df2 = df2.sort_values('凭证编号', ascending=False)
    df2.reset_index(drop=True, inplace=True)
    df3 = df2.drop_duplicates(['凭证编号'], keep='first')
    for i in df3['凭证编号'].values:
        bh = df2[df2['凭证编号'] == i]
        if bh.shape[0] > 0:
            fj = df2[df2["附件编号"].str.len() < 4]
            if fj.shape[0] > 0:
                fj_num = bh.shape[0] - fj.shape[0]
            else:
                fj_num = bh.shape[0]
            df2.ix[bh.index.min(), '附件个数'] = fj_num
    print(df2)

    # 读取其他来源
    path_file = path + '/'
    lists = os.listdir(path_file)
    df5 = pd.DataFrame(columns=['公司名称',	'凭证张数',	'凭证编号',	'附件个数',	'附件编号'])
    for filename in lists:
        if 'CUX_邮速整合总账凭证' in filename:
            app = xw.App(visible=False, add_book=False)
            target4 = app.books.open(path + '/' + filename)
            sht4 = target4.sheets[0]
            rows4 = sht4.used_range.last_cell.row
            cols4 = sht4.used_range.last_cell.column
            col_name4 = get_column_letter(cols4)
            fanwei4 = 'A5:' + str(col_name4) + str(rows4)
            gv_excel4 = target4.sheets[0].range(fanwei4).value
            target4.close()
            df4 = pd.DataFrame(gv_excel4, columns=gv_excel4[0])
            df4 = df4.drop(index=[0])
            df4 = df4[df4["来源"].str.len() > 1]
            print(df4)
            df4 = df4[['凭证编号', '附件编号']]
            if '应付账款' in filename:
                for m in df4.index.values:
                    if '号' in df4.loc[m]['附件编号']:
                        print(df4.loc[m]['附件编号'])
                        numbers1 = df4.loc[m]['附件编号'][3:]
                        numbers2 = numbers1[:-7]
                        df4.ix[m, '附件编号'] = numbers2
                    else:
                        continue
            df4.insert(loc=0, column='凭证张数', value='')
            df4.insert(loc=0, column='公司名称', value=dept)
            df4.insert(loc=3, column='附件个数', value='')
            df5 = pd.concat([df4, df5])
        else:
            continue
    df5 = df5.drop_duplicates(keep='first')
    df5 = df5.sort_values('凭证编号', ascending=False)
    df5.reset_index(drop=True, inplace=True)
    df6 = df5.drop_duplicates(['凭证编号'], keep='first')
    for j in df6['凭证编号'].values:
        bh5 = df5[df5['凭证编号'] == j]
        if bh5.shape[0] > 0:
            bh6 = bh5.drop_duplicates(['附件编号'], keep='first')
            if bh6.shape[0] == 1:
                fj6 = bh6[bh6["附件编号"].str.len() < 4]
                if fj6.shape[0] > 0:
                    fj_num5 = 0
                else:
                    fj_num5 = 1
            else:
                fj6 = bh6[bh6["附件编号"].str.len() < 4]
                if fj6.shape[0] > 0:
                    fj_num5 = bh6.shape[0] - fj6.shape[0]
                else:
                    fj_num5 = bh6.shape[0]
            df5.ix[bh6.index.min(), '附件个数'] = fj_num5
    print(df5)
    df7 = pd.concat([df2, df5])
    df7['公司名称'] = dept
    df7['凭证张数'] = ''

    zzpath = path + '/CUX_凭证编号索引表.xls'
    app = xw.App(visible=False, add_book=False)
    target8 = app.books.open(zzpath)
    sht8 = target8.sheets[0]
    rows8 = sht8.used_range.last_cell.row
    cols8 = sht8.used_range.last_cell.column
    col_name8 = get_column_letter(cols8)
    fanwei8 = 'A4:' + str(col_name8) + str(rows8)
    gv_excel8 = target8.sheets[0].range(fanwei8).value
    target8.close()
    df8 = pd.DataFrame(gv_excel8, columns=gv_excel8[0])
    df8 = df8.drop(index=[0])
    df8 = df8.drop_duplicates(['凭证号'], keep='first')
    for k in df8['凭证号'].values:
        if len(str(k)) > 8:
            if len(str(k)) > 18:
                k = str(k)[1:]
                k = str(k)[:-1]
            if str(k) in df7['凭证编号'].values:
                continue
            else:
                print(k)
                alist = [dept, '', k, 0, '']
                df7.loc[len(df7)] = alist
                pass
        else:
            pass
    df7.reset_index(drop=True, inplace=True)
    print(df7)

    # 读取凭证pdf并分离
    pdf_reader = PdfFileReader(path + '/CUX_凭证打印.pdf')
    with pdfplumber.open(path + '/CUX_凭证打印.pdf') as pdf:
        for i in range(df7.shape[0]):
            # print(str(df['凭证张数'].values[i]))
            if len(str(df7['附件个数'].values[i])) >= 1:
                # print(i)
                num_a = 1
                num_b = 0
                for j in range(pdf_reader.getNumPages()):
                    page = pdf.pages[j]
                    pdf_writer = PdfFileWriter()
                    # print(page.extract_text())
                    if str(df7['凭证编号'].values[i]) in page.extract_text():
                        pdf_writer.addPage(pdf_reader.getPage(j))
                        # print(j + 1, page.extract_text())
                        try:
                            if '财务主管' not in page.extract_text():
                                pdf_writer.addPage(pdf_reader.getPage(j + 1))
                        except Exception as e:
                            print(e)
                        with open(pzpath + '/' + str(df7['凭证编号'].values[i]) + '_' + str(num_a) + '.pdf', 'wb') as out:
                            pdf_writer.write(out)
                        df7.ix[i, '凭证张数'] = num_a
                        num_a += 1
                        num_b = 1
                if num_a == 1 and num_b == 1:
                    os.rename(pzpath + '/' + str(df7['凭证编号'].values[i]) + '_' + str(num_a) + '.pdf', pzpath + '/' + str(df7['凭证编号'].values[i]) + '.pdf')
            else:
                continue

    # 凭证与附件写入总索引表
    df7.to_excel(allpath, sheet_name='Sheet1', index=False, startrow=1, startcol=0)


def date_input():
    now = arrow.now()
    nowtime = now.shift(days=0).format('YYYY-MM')
    root = tk.Tk()
    root.title('请输入年月：')
    root.geometry('300x100')
    txt = tk.StringVar()
    b1 = tk.Entry(root, width=20, textvariable=txt)
    b1.pack()
    b3 = tk.Label(root, text="格式为：2022-10")
    b3.pack()

    def get_entry():
        global x
        x = b1.get()
        if len(x) == 7:
            if x[4] == '-' and x <= nowtime:
                root.destroy()
            else:
                b4 = tk.Label(root, text="格式或日期错误")
                b4.pack()

        else:
            b4 = tk.Label(root, text="格式错误")
            b4.pack()

    root.bind('<Return>', get_entry)
    b2 = tk.Button(root, text='确定', command=get_entry)
    b2.pack()
    root.mainloop()
    return x


def win_on():
    def get_all_hwnd(hwnd, mouse):
        if (win32gui.IsWindow(hwnd) and
                win32gui.IsWindowEnabled(hwnd) and
                win32gui.IsWindowVisible(hwnd)):
            hwnd_map.update({hwnd: win32gui.GetWindowText(hwnd)})

    hwnd_map = {}
    win32gui.EnumWindows(get_all_hwnd, 0)

    for h, t in hwnd_map.items():
        if t:
            if t == 'PZ':
                # h 为想要放到最前面的窗口句柄
                print(h)

                win32gui.BringWindowToTop(h)
                shell = win32com.client.Dispatch("WScript.Shell")
                shell.SendKeys('%')

                # 被其他窗口遮挡，调用后放到最前面
                win32gui.SetForegroundWindow(h)

                # 解决被最小化的情况
                win32gui.ShowWindow(h, win32con.SW_RESTORE)


def main():
    erp_index()


ctypes.windll.user32.ShowWindow(ctypes.windll.kernel32.GetConsoleWindow(), 6)
nowdate = date_input()
if os.path.exists('D:/PZIndex/' + nowdate):
    pass
else:
    os.mkdir('D:/PZIndex/' + nowdate)
frame = pd.read_excel('D:/PZSets/user.xlsx')
dept = frame['单位代码'].values[0]
username = frame['ERP用户名'].values[0]
psw = frame['密码'].values[0]
if os.path.exists('D:/PZIndex/' + nowdate + '/' + dept):
    pass
else:
    os.mkdir('D:/PZIndex/' + nowdate + '/' + dept)
path = 'D:/PZIndex/' + nowdate + '/' + dept + '/BaseData'
pzpath = 'D:/PZIndex/' + nowdate + '/' + dept + '/PZ'
if os.path.exists(path):
    pass
else:
    os.mkdir(path)
if os.path.exists(pzpath):
    pass
else:
    os.mkdir(pzpath)
allpath = 'D:/PZIndex/' + nowdate + '/' + dept + '/PZ/凭证编号与附件编号对应表.xlsx'
if os.path.exists(allpath):
    pass
else:
    shutil.copy('D:/PZSets/凭证编号与附件编号对应表.xlsx', allpath)

main()
